package org.kl.bf.service.basic;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpSession;

import org.apache.commons.lang3.StringUtils;
import org.kl.bf.dto.MyConnectionFactory;
import org.kl.bf.entity.basic.CodeValues;
import org.kl.bf.entity.basic.ExcelConfig;
import org.kl.bf.entity.basic.ExcelImport;
import org.kl.bf.entity.basic.ExcelImportFile;
import org.kl.bf.entity.easyui.Json;
import org.kl.bf.repository.basic.CodeValuesDao;
import org.kl.bf.repository.basic.ExcelConfigDao;
import org.kl.bf.repository.basic.ExcelImportDao;
import org.kl.bf.repository.basic.ExcelImportFileDao;
import org.kl.bf.repository.basic.ExcelJDBCDao;
import org.kl.bf.service.ExcelImportLogger;
import org.kl.bf.service.ServiceException;
import org.kl.bf.utils.BasicConstants;
import org.kl.bf.utils.CRUDUtils;
import org.kl.bf.utils.CollectionUtils;
import org.kl.bf.utils.ExcelReader;
import org.kl.bf.utils.KLDynamicSpecifications;
import org.kl.bf.utils.KLSearchFilter;
import org.kl.bf.web.base.BasicController;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springside.modules.utils.Collections3;

import com.alibaba.fastjson.JSON;

/**
 * @Author hqm
 * @CreateTime 2014-3-13 下午9:11:30
 */
// Spring Bean的标识.
@Component
// 默认将类中的所有public函数纳入事务管理.
@Transactional
public class ExcelConfigService {

	private ExcelConfigDao excelConfigDao;
	private ExcelImportDao excelImportDao;
	private ExcelImportFileDao excelImportFileDao;
	private ExcelJDBCDao excelJDBCDao;
	private ExcelImportLogger excelImportLogger;
	private CodeValuesDao codevaluesDao;

	public ExcelConfig getExcelConfig(String guid) {
		return excelConfigDao.findOne(guid);
	}

	public void saveExcelConfig(ExcelConfig entity) {
		excelConfigDao.save(entity);
	}

	public void deleteExcelConfig(String guid) {
		excelConfigDao.delete(guid);
	}

	/**
	 * 删除多条记录
	 * 
	 * @param ids
	 */
	public String multiDelete(String ids) {
		String[] idArray = StringUtils.split(ids, ",");
		int i = 0;
		for (String id : idArray) {
			if (StringUtils.isNotBlank(id)) {
				excelConfigDao.delete(id);
				i++;
			}
		}
		return "删除" + i + "条记录";
	}

	public List<ExcelConfig> getAllExcelConfig() {
		return excelConfigDao.findAll();
	}

	public List<ExcelConfig> findByExcelImportId(String excelImportId) {
		return excelConfigDao.findByExcelImportIdOrderByColumnNoAsc(excelImportId);
	}

	public Page<ExcelConfig> getUserExcelConfig(Map<String, Object> searchParams, int pageNumber, int pageSize, String sortType,
			String orderType) {
		PageRequest pageRequest = CRUDUtils.buildPageRequest(pageNumber, pageSize, sortType, orderType);
		Specification<ExcelConfig> spec = buildSpecification(searchParams);

		return excelConfigDao.findAll(spec, pageRequest);
	}

	public Map<Integer, ExcelConfig> findConfigMapByExcelImportId(String excelImportId) {
		List<ExcelConfig> ecList = excelConfigDao.findByExcelImportIdOrderByColumnNoAsc(excelImportId);
		Map<Integer, ExcelConfig> ecMap = new HashMap<Integer, ExcelConfig>();
		for (ExcelConfig ec : ecList) {
			ecMap.put(ec.getColumnNo(), ec);
		}
		return ecMap;
	}

	public int[] importExcel(String importNo, String excelImportFileId) {
		ExcelImport ei = excelImportDao.findByImportNo(importNo);
		ExcelImportFile a = excelImportFileDao.findOne(excelImportFileId);
		if (ei != null) {
			Map<Integer, ExcelConfig> ecMap = findConfigMapByExcelImportId(ei.getId());
			if (!ecMap.isEmpty()) {
				int notIssistColumnCount = 0;
				for (Integer key : ecMap.keySet()) {
					ExcelConfig ec = ecMap.get(key);
					if (ec.getIsAssistColumn() != BasicConstants.YES_INT) {
						notIssistColumnCount++;
					}
					if (ec != null && ec.getIsForeignKey() == BasicConstants.NO_INT && StringUtils.isNotBlank(ec.getConvertMap())) {
						ec.setColumnConvertMap(CollectionUtils.StringToMap(ec.getConvertMap()));
					}
				}
				List<String[]> excelData = new ExcelReader(a.getFilePath()).getAllData(0);
				if (Collections3.isEmpty(excelData)) {
					String msg = "导入文件内容为空";
					excelImportLogger.log("ExcelConfig", ExcelImportLogger.LoggerAction.FORMET_CHECKS.getActionName(),
							BasicController.getCurrentUserName(), new Json(msg));
					throw new ServiceException(msg);
				}
				if (excelData.get(0).length != ecMap.size()) {
					excelImportLogger.log("ExcelConfig", ExcelImportLogger.LoggerAction.FORMET_CHECKS.getActionName(),
							BasicController.getCurrentUserName(),
							new Json("导入数据列数与模板不相同，模板列数[" + ecMap.size() + "]，导入数据列数[" + excelData.get(0).length + "]"));
				}
				return excelJDBCDao.importDBFromExcel(ei, ecMap, notIssistColumnCount, excelData);
			}
		}
		return null;
	}

	/**
	 * 创建动态查询条件组合.
	 */
	private Specification<ExcelConfig> buildSpecification(Map<String, Object> searchParams) {
		Map<String, KLSearchFilter> filters = KLSearchFilter.parse(searchParams);
		Specification<ExcelConfig> spec = KLDynamicSpecifications.byKLSearchFilter(filters.values(), ExcelConfig.class);
		return spec;
	}

	/**
	 * 通用查改增删方法
	 * 
	 * @param effectRow
	 */
	public void crud(String inserted, String updated, String deleted) {
		// // “新增”和“修改”时检查项目是否唯一
		List<ExcelConfig> inserts = JSON.parseArray(inserted, ExcelConfig.class);
		if (Collections3.isNotEmpty(inserts)) {
			for (ExcelConfig a : inserts) {
				excelConfigDao.save(a);
			}
		}
		List<ExcelConfig> updates = JSON.parseArray(updated, ExcelConfig.class);
		if (updates.size() > 0) {
			for (ExcelConfig b : updates) {
				System.out.println(b.toString());
				excelConfigDao.save(b);
			}
		}
		List<ExcelConfig> deletes = JSON.parseArray(deleted, ExcelConfig.class);
		if (Collections3.isNotEmpty(deletes)) {
			excelConfigDao.deleteInBatch(deletes);
		}
	}

	@Autowired
	public void setExcelConfigDao(ExcelConfigDao excelConfigDao) {
		this.excelConfigDao = excelConfigDao;
	}

	@Autowired
	public void setExcelImportDao(ExcelImportDao excelImportDao) {
		this.excelImportDao = excelImportDao;
	}

	@Autowired
	public void setExcelImportFileDao(ExcelImportFileDao excelImportFileDao) {
		this.excelImportFileDao = excelImportFileDao;
	}

	@Autowired
	public void setExcelJDBCDao(ExcelJDBCDao excelJDBCDao) {
		this.excelJDBCDao = excelJDBCDao;
	}

	@Autowired
	public void setExcelImportLogger(ExcelImportLogger excelImportLogger) {
		this.excelImportLogger = excelImportLogger;
	}

	@Autowired
	public void setCodevaluesDao(CodeValuesDao codevaluesDao) {
		this.codevaluesDao = codevaluesDao;
	}

	// ***********************************************************************自动生成功能的实现****************************************
	public String bulid(String id, HttpSession session) {
		ExcelImport excelImport = excelImportDao.find(ExcelImport.class, id);
		List<ExcelConfig> deleteList = excelConfigDao.findByExcelImportId(id);
		List<ExcelConfig> excelConfigList = new ArrayList<ExcelConfig>();
		List<CodeValues> codeValuesList = codevaluesDao.findByCodeTableId(BasicConstants.TableNo_JAVATYPE);
		excelConfigList = getConnection(excelImport, session, codeValuesList);
		int i = deleteList.size();
		int j = excelConfigList.size();
		excelConfigDao.delete(deleteList);
		excelConfigDao.save(excelConfigList);
		return "清除" + i + "条已存在的记录，自动生成" + j + "记录";
	}

	public static List<ExcelConfig> getConnection(ExcelImport excelImport, HttpSession session, List<CodeValues> codeValuesList) {
		MyConnectionFactory factory = MyConnectionFactory.getConnFactoryInstance(session);
		Connection conn = factory.getConn();
		List<ExcelConfig> excelConfigList = new ArrayList<ExcelConfig>();
		try {
			if (factory.getDriver().contains("oracle")) {
				PreparedStatement pst = conn.prepareStatement("select * from " + excelImport.getTableName());
				ResultSet rs = pst.executeQuery();
				ResultSetMetaData data = rs.getMetaData();
				int n = 1;
				for (int i = 1; i <= data.getColumnCount(); i++) {
					if (BasicConstants.The_Culum_Name_Not_Input_BIG.contains(data.getColumnName(i))) {
						continue;
					}
					// 获得指定列的列名
					String columnName = data.getColumnName(i);
					// 获得指定列的类型名
					String dataType = data.getColumnTypeName(i);
					// 获得指定列的小数点精度
					int scale = data.getScale(i);
					Statement stmt = conn.createStatement();
					String sql = "select b.comments as \"Comment\" from all_col_comments b where b.Table_Name='"
							+ excelImport.getTableName().toUpperCase() + "'" + "     and b.owner='" + factory.getUsername().toUpperCase()
							+ "'" + "     and b.COLUMN_NAME = '" + columnName.toUpperCase() + "'";
					ResultSet rsd = stmt.executeQuery(sql);
					while (rsd.next()) {
						ExcelConfig excelConfig = new ExcelConfig();
						excelConfig.setExcelImport(excelImport);
						excelConfig.setChineseName(rsd.getString("Comment"));
						for (int j = 0; j < codeValuesList.size(); j++) {
							if (StringUtils.equals(codeValuesList.get(j).getCodeName(), changeType(dataType, scale))) {
								excelConfig.setCodeValues(codeValuesList.get(j));
								break;
							}
						}
						excelConfig.setColumnNo(n);
						excelConfig.setColumnName(data.getColumnName(i).toLowerCase());
						excelConfig.setIsUnionUnique(BasicConstants.NO_INT);
						excelConfig.setIsForeignKey(BasicConstants.NO_INT);
						excelConfig.setIsAssistColumn(BasicConstants.NO_INT);
						excelConfigList.add(excelConfig);
						n++;
					}
				}
				MyConnectionFactory.close(conn, pst, rs);
			} else if (factory.getDriver().contains("mysql")) {
				Statement stmt = conn.createStatement();
				String sql = "select COLUMN_NAME,DATA_TYPE,COLUMN_COMMENT from INFORMATION_SCHEMA.Columns where table_name='"
						+ excelImport.getTableName() + "' and table_schema='" + factory.getUrl().substring(27, 41) + "'";
				ResultSet rsd = stmt.executeQuery(sql);
				int i = 1;
				while (rsd.next()) {
					if (BasicConstants.The_Culum_Name_Not_Input_SMALL.contains(rsd.getString("COLUMN_NAME"))) {
						continue;
					}
					ExcelConfig excelConfig = new ExcelConfig();
					excelConfig.setExcelImport(excelImport);
					excelConfig.setChineseName(rsd.getString("COLUMN_COMMENT"));
					for (int j = 0; j < codeValuesList.size(); j++) {
						if (StringUtils.equals(codeValuesList.get(j).getCodeName(), changeType(rsd.getString("DATA_TYPE"), 1))) {
							excelConfig.setCodeValues(codeValuesList.get(j));
							break;
						}
					}
					excelConfig.setColumnNo(i);
					excelConfig.setIsUnionUnique(BasicConstants.NO_INT);
					excelConfig.setIsForeignKey(BasicConstants.NO_INT);
					excelConfig.setIsAssistColumn(BasicConstants.NO_INT);
					excelConfig.setColumnName(rsd.getString("COLUMN_NAME").toLowerCase());
					excelConfigList.add(excelConfig);
					i++;
				}
				MyConnectionFactory.close(conn, null, rsd);
			} else {
				return null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return excelConfigList;
	}

	private static String changeType(String datatype, int number) {
		switch (datatype.toLowerCase()) {
		case "varchar2":
			return "String";
		case "number":
			if (number == 0) {
				return "Integer";
			} else {
				return "Double";
			}
		case "date":
			return "Date";
		case "int":
			return "Integer";
		case "varchar":
			return "String";
		case "datetime":
			return "Date";
		default:
			return null;
		}
	}

	public List<ExcelConfig> getAllConfig(Map<String, Object> searchParams,String sortType, String orderType) {
		Specification<ExcelConfig> spec = KLDynamicSpecifications.buildSpecification(searchParams, ExcelConfig.class);
		return excelConfigDao.findAll(spec, CRUDUtils.buildSort(sortType, orderType));
	}
}
